package com.zhou.spider;

import com.fasterxml.jackson.databind.ObjectMapper;
import junit.framework.TestCase;
import org.apache.commons.io.FileUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.junit.Assert;
import org.seimicrawler.xpath.JXDocument;
import us.codecraft.webmagic.selector.Html;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: zhouweixin
 * @Description:
 * @Date: Created in 23:33 2018/12/29
 * @Modified By:
 */
public class Main extends TestCase {
    private final String rootUrl = "https://bulkdata.uspto.gov/data/patent/officialgazette/";
    private final String dataRoot = "data/";
    private final int startYear = 2002;
    private final int endYear = 2019;
    private final String dataUrl = "data.accdb";

    /**
     * 入口1：下载压缩文件列表
     *
     * @throws IOException
     */
    public void testDownloadFileName() throws IOException {
        for (int year = startYear; year <= endYear; year++) {
            String url = rootUrl + year;

            Document document = Jsoup.connect(url).get();

            Html html = new Html(document.toString());
            List<String> names = html.xpath("//div[@id='usptoGlobalHeader']/div[@class='container']/table[2]/tbody/tr/td[1]/a/@href").all();
            List<String> sizeStrs = html.xpath("//div[@id='usptoGlobalHeader']/div[@class='container']/table[2]/tbody/tr/td[2]/text()").all();

            Assert.assertTrue(names.size() == sizeStrs.size());

            List<Map<String, Object>> list = new ArrayList<>();
            for (int i = 0; i < sizeStrs.size(); i++) {
                String name = names.get(i);
                int size = Integer.parseInt(sizeStrs.get(i));
                HashMap<String, Object> map = new HashMap<>();
                map.put("name", name);
                map.put("size", size);
                list.add(map);
            }

            saveFileNames(year, list);
        }
    }

    /**
     * 保存文件名
     *
     * @param year
     * @param downloads
     * @throws IOException
     */
    private void saveFileNames(int year, List downloads) throws IOException {
        new File(dataRoot).mkdirs();
        String filename = dataRoot + year + ".json";

        ObjectMapper objectMapper = new ObjectMapper();
        objectMapper.writeValue(new File(filename), downloads);
        System.out.println("保存：" + filename + ", 数量：" + downloads.size());
    }

    /**
     * 入口2：下载文件
     *
     * @throws IOException
     */
    public void testDownloadFiles() throws IOException {
        for (int year = startYear; year <= endYear; year++) {
            List<Map<String, Object>> list = getNamesFromJson(year);
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);
                String name = (String) map.get("name");
                int size = (int) map.get("size");
                download(year, name, size, String.format("【%d: %d/%d】", year, i + 1, list.size()));
            }
        }
    }

    /**
     * 通过年份下载
     *
     * @param year
     * @throws IOException
     */
    public void downloadByYear(int year) {
        try {
            List<Map<String, Object>> list = getNamesFromJson(year);
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);
                String name = (String) map.get("name");
                int size = (int) map.get("size");
                download(year, name, size, String.format("【%d: %d/%d】", year, i + 1, list.size()));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Map<Integer, Integer> getYear2num() {
        Map<Integer, Integer> year2num = new HashMap<>();

        if (new File(dataRoot).exists() == false) {
            return year2num;
        }

        String[] names = new File(dataRoot).list((dir, name) -> name.endsWith(".json"));

        for (String name : names) {
            int year = Integer.parseInt(name.substring(0, 4));
            try {
                List<Map<String, Object>> list = getNamesFromJson(year);
                year2num.put(year, list.size());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return year2num;
    }

    /**
     * 从json文件里读取文件名
     *
     * @param year
     * @throws IOException
     */
    public List<Map<String, Object>> getNamesFromJson(int year) throws IOException {
        String filename = dataRoot + year + ".json";
        if (new File(filename).exists() == false) {
            return new ArrayList<>();
        }
        ObjectMapper objectMapper = new ObjectMapper();
        List<Map<String, Object>> list = objectMapper.readValue(new File(filename), List.class);

        return list;
    }

    /**
     * 下载
     *
     * @param year
     * @param name
     * @param msg
     */
    public void download(int year, String name, final int size, String msg) {
        new File(dataRoot + year).mkdirs();

        String url = rootUrl + year + "/" + name;
        String filename = dataRoot + year + "/" + name;

        if (new File(filename).exists()) {
            System.out.println(name + "已下载");
            return;
        }

        try {
            org.jsoup.Connection.Response response = Jsoup.connect(url).ignoreContentType(true).maxBodySize(999999999).timeout(1000 * 60 * 60 * 24).ignoreHttpErrors(true).execute();

            BufferedInputStream bis = response.bodyStream();
            BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(filename + ".downloading"));

            int sum = 0;
            while (true) {
                byte[] bytes = new byte[1024 * 1024];
                int n = bis.read(bytes);
                if (n < 0) {
                    break;
                }

                sum += n;
                bos.write(bytes, 0, n);

                double downloaded = 100.0 * sum / size;
                System.out.println(String.format("%s%s: %.2f%%", msg, name, downloaded));
            }
            bos.close();
            bis.close();
            new File(filename + ".downloading").renameTo(new File(filename));
            System.out.println("下载成功：" + name);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 入口3：提取专利号
     */
    public void testExtractPatentNumbers() {
        List<String> selectClcs = Arrays.asList("A61", "C12", "G01");

        File rootDir = new File(dataRoot);
        if (rootDir.exists() == false) {
            System.out.println(dataRoot + "不存在");
            return;
        }

        File[] yearDirs = rootDir.listFiles(f -> f.isDirectory());
        if (yearDirs == null || yearDirs.length == 0) {
            System.out.println("【" + rootDir + "】目录下没有年份文件夹！");
            return;
        }
        for (File yearDir : yearDirs) {
            System.out.println(yearDir.getName());
            File[] zipDirs = yearDir.listFiles(f -> f.isDirectory());
            if (zipDirs == null || zipDirs.length == 0) {
                System.out.println("【" + yearDir + "】目录下没有解压好的数据文件夹！");
                return;
            }
            for (File zipDir : zipDirs) {
                String dir = zipDir.getAbsolutePath();
                String dirname = zipDir.getName();
                String code = dirname.split("_")[1];

                for (String s : selectClcs) {
                    String body = String.format("%s\\%s\\OG\\classification\\cpcClassGroup_%sBody.html", dir, code, s);
                    File file = new File(body);
                    if (file.exists() == false) {
                        System.err.println("未找到：" + s);
                        continue;
                    }

                    try {
                        String content = FileUtils.readFileToString(file);
                        List<String> patentNums = new Html(content).xpath("//table/tbody/tr/td[3]/a/text()").all();

                        patentNums = patentNums.stream().map(patentNum -> {
                            int idx = 0;
                            for (int i = 0; i < patentNum.length(); i++) {
                                if (patentNum.charAt(i) != '0') {
                                    idx = i;
                                    break;
                                }
                            }
                            patentNum = patentNum.substring(idx);
                            return patentNum;
                        }).collect(Collectors.toList());

                        savePatentNumbers(patentNums);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    /**
     * 获取专利号
     *
     * @return
     * @throws IOException
     */
    public Set<String> getPatentNums() throws IOException {
        Set<String> set = new HashSet<>();

        String root = "D:\\助研\\罗侦\\20181226\\";
        String dir = "e-OG20180102_1446-1";

        File file = new File(root + dir);
        File[] files = file.listFiles((d, n) -> n.endsWith(".pn"));

        for (File f : files) {
            List<String> list = (List<String>) FileUtils.readLines(f);
            set.addAll(list);
        }

        return set;
    }

    public void savePatentNumbers(List<String> patentNumbers) {
        int sum = patentNumbers.size();
        Connection conn = createConnection();

        String sql = "insert into 专利号 (专利号) values (?)";
        try {

            Statement stmt = conn.createStatement();

            // 删除重复值
            Iterator<String> it = patentNumbers.iterator();
            while (it.hasNext()) {
                String pn = it.next();
                ResultSet rs = stmt.executeQuery("select count(*) from 专利号 where 专利号='" + pn + "'");
                rs.next();
                int num = rs.getInt(1);
                if (num > 0) {
                    it.remove();
                }
            }

            PreparedStatement pstmt = conn.prepareStatement(sql);
            for (String patentNumber : patentNumbers) {
                pstmt.setString(1, patentNumber);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("存储专利号个数(" + sum + ")：" + patentNumbers.size());
        closeConn(conn);
    }

    /**
     * 入口4：下载数据页面
     */
    public void testDownloadHtmlByPatentNum() {
        new File("html").mkdir();

        Connection conn = createConnection();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs1 = stmt.executeQuery("select count(distinct 专利号) from 专利号");
            rs1.next();
            int size = rs1.getInt(1);
            ResultSet rs = stmt.executeQuery("select distinct 专利号 from 专利号");
            while (rs.next()) {
                int num = rs.getRow();
                String patentNumer = rs.getString(1);

                String url = "http://patft.uspto.gov/netacgi/nph-Parser?Sect1=PTO1&Sect2=HITOFF&d=PALL&p=1&u=%2Fnetahtml%2FPTO%2Fsrchnum.htm&r=1&f=G&l=50&s1=" + patentNumer + ".PN.&OS=PN/" + patentNumer + "&RS=PN/" + patentNumer;
                try {
                    org.jsoup.Connection.Response res = Jsoup.connect(url)
                            .userAgent("Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36")
                            .execute();
                    String html = res.body();

                    String htmlFileName = "html/" + patentNumer + ".html";
                    File file = new File(htmlFileName);
                    if (file.exists()) {
                        continue;
                    }
                    FileUtils.writeStringToFile(file, html);
                    // 保存下载成功的记录
                    System.out.println(String.format("下载进度：%d / %d", num, size));
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 入口5：提取专利数据
     */
    public void testExtractPatentFromHtml() {
        File file = new File("html");
        if (file.exists() == false) {
            return;
        }

        File[] htmls = file.listFiles(f -> f.getName().endsWith(".html"));
        for (File htmlFile : htmls) {
            Patent patent = new Patent();
            try {
                String content = FileUtils.readFileToString(htmlFile);
                Html html = new Html(content);
                patent.patentType = html.xpath("/html/body/table[2]/tbody/tr[1]/td[@align='left']/b/text()").get();
                patent.patentNum = html.xpath("/html/body/table[2]/tbody/tr[1]/td[@align='right']/b/text()").get();
                patent.firstInventor = html.xpath("/html/body/table[2]/tbody/tr[2]/td[@align='left']/b/text()").get();
                patent.pubDate = html.xpath("/html/body/table[2]/tbody/tr[2]/td[@align='right']/b/text()").get();

                patent.name = html.xpath("/html/body/font/text()").get();

                JXDocument jxDocument = JXDocument.create(content);
                Object inventor = jxDocument.selOne("//table/tbody/tr/th[starts-with(text(), 'Inventors:')]/following-sibling::td[1]");
                if (inventor != null) {
                    patent.inventor = inventor.toString();
                }

                Object table = jxDocument.selOne("//table/tbody/tr/th[starts-with(text(), 'Applicant:')]/following-sibling::td[1]/table[1]");

                if (table != null) {
                    String tr = table.toString();
                    tr = tr.replace("<br>", "-|-").replace("</b>", "").replace("<b>", "");


                    Html ht = new Html(tr);
                    String str = "";
                    str += ht.xpath("//table/tbody/tr[2]/td[1]/text()").get() + "-||-";
                    str += ht.xpath("//table/tbody/tr[2]/td[2]/text()").get() + "-||-";
                    str += ht.xpath("//table/tbody/tr[2]/td[3]/text()").get() + "-||-";
                    str += ht.xpath("//table/tbody/tr[2]/td[4]/text()").get() + "-||-";
                    str += ht.xpath("//table/tbody/tr[2]/td[5]/text()").get();
                    patent.applicant = str;
                }

                Object assignee = jxDocument.selOne("//table/tbody/tr/th[starts-with(text(), 'Assignee:')]/following-sibling::td[1]");
                if (assignee != null)
                    patent.assignee = assignee.toString();

                Object familyId = jxDocument.selOne("//table/tbody/tr/th[starts-with(text(), 'Family ID:')]/following-sibling::td[1]/b/text()");
                if (familyId != null)
                    patent.familyId = familyId.toString();

                Object appNum = jxDocument.selOne("//table/tbody/tr/th[starts-with(text(), 'Appl. No.:')]/following-sibling::td[1]/b/text()");
                if (appNum != null)
                    patent.appNum = appNum.toString();

                Object appDate = jxDocument.selOne("//table/tbody/tr/th[starts-with(text(), 'Filed:')]/following-sibling::td[1]/b/text()");
                if (appDate != null)
                    patent.appDate = appDate.toString();

                Object usClc = jxDocument.selOne("//table/tbody/tr/td/b[starts-with(text(), 'Current U.S. Class:')]/parent::td/following-sibling::td[1]/b/text()");
                if (usClc != null)
                    patent.usClc = usClc.toString();

                Object cpcClc = jxDocument.selOne("//table/tbody/tr/td/b[starts-with(text(), 'Current CPC Class:')]/parent::td/following-sibling::td[1]/text()");
                if (cpcClc != null)
                    patent.cpcClc = cpcClc.toString();

                Object ipcClc = jxDocument.selOne("//table/tbody/tr/td/b[starts-with(text(), 'Current International Class:')]/parent::td/following-sibling::td[1]/text()");
                if (ipcClc != null)
                    patent.ipcClc = ipcClc.toString();

                patent.preprocess();

                List<Object> trs = JXDocument.create(content).sel("//center/b[starts-with(text(), 'U.S. Patent Documents')]/parent::center/following-sibling::table[1]/tbody/tr");
                int n = saveReUsPatent(patent.patentNum, trs);
                if (n > 0) {
                    patent.reUsPatent = "被引用美国专利" + patent.patentNum + ".xlsx";
                }

                trs = JXDocument.create(content).sel("//center/b[starts-with(text(), 'Foreign Patent Documents')]/parent::center/following-sibling::table[1]/tbody/tr");
                n = saveReFoPatent(patent.patentNum, trs);
                if (n > 0) {
                    patent.reFoPatent = "被引用外国专利" + patent.patentNum + ".xlsx";
                }

                Object reOther = JXDocument.create(content).selOne("//center/b[starts-with(text(), 'Other References')]/parent::center/following-sibling::table[1]/tbody/tr/td");
                if (reOther != null) {
                    new File("非专利参考文献（其他参考文献）").mkdir();
                    patent.reOther = "非专利参考文献（其他参考文献）/" + patent.patentNum + ".txt";
                    FileUtils.writeStringToFile(new File(patent.reOther), reOther.toString());

                }

                savePatent(patent);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


    }

    /**
     * 保存专利数据
     *
     * @param patent
     */
    private void savePatent(Patent patent) {
        Connection conn = createConnection();

        String sql = "insert into 美国医药制造业专利数据 (" +
                "[专利类型]," +
                "[专利号码]," +
                "[第一发明人]," +
                "[专利公告日期]," +
                "[专利名称]," +
                "[发明人]," +
                "[申请人]," +
                "[专利权人]," +
                "[专利族ID]," +
                "[专利申请号]," +
                "[专利申请日期]," +
                "[美国分类号]," +
                "[CPC分类号]," +
                "[IPC分类号]," +
                "[被引用美国专利]," +
                "[被引用外国专利]," +
                "[非专利参考文献（其他参考文献）]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select count(*) from 美国医药制造业专利数据 where 专利号码='" + patent.patentNum + "'");
            if (rs.next()) {
                if (rs.getInt(1) > 0) {
                    System.out.println("已存在：" + patent.patentNum);
                    return;
                }
            }

            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, patent.patentType);
            pstmt.setString(2, patent.patentNum);
            pstmt.setString(3, patent.firstInventor);
            pstmt.setString(4, patent.pubDate);
            pstmt.setString(5, patent.name);
            pstmt.setString(6, patent.inventor);
            pstmt.setString(7, patent.applicant);
            pstmt.setString(8, patent.assignee);
            pstmt.setString(9, patent.familyId);
            pstmt.setString(10, patent.appNum);
            pstmt.setString(11, patent.appDate);
            pstmt.setString(12, patent.usClc);
            pstmt.setString(13, patent.cpcClc);
            pstmt.setString(14, patent.ipcClc);
            pstmt.setString(15, patent.reUsPatent);
            pstmt.setString(16, patent.reFoPatent);
            pstmt.setString(17, patent.reOther);
            pstmt.execute();
            System.out.println("存储：" + patent.patentNum);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        closeConn(conn);
    }

    private int saveReFoPatent(String patentNum, List<Object> trs) {
        new File("被引用外国专利").mkdir();
        String name = "被引用外国专利/" + patentNum + ".xlsx";

        List<Value3> value3s = new ArrayList<>();
        for (Object tr : trs) {
            JXDocument jxDocument = JXDocument.create("<table>" + tr.toString() + "</table>");
            Object o1 = jxDocument.selOne("//tr/td[2]/text()");
            Object o2 = jxDocument.selOne("//tr/td[4]/text()");
            Object o3 = jxDocument.selOne("//tr/td[6]/text()");
            if (o1 == null && o2 == null && o3 == null) {
                continue;
            }

            String value1 = "";
            String value2 = "";
            String value3 = "";
            if (o1 != null) {
                value1 = o1.toString().trim();
            }
            if (o2 != null) {
                value2 = o2.toString().trim();
            }
            if (o3 != null) {
                value3 = o3.toString().trim();
            }

            if (value1.equals("") && value2.equals("") && value3.equals("")) {
                continue;
            }

            value3s.add(new Value3(value1, value2, value3));
        }


        if (value3s.size() == 0) {
            return 0;
        }

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();

        for (int i = 0; i < value3s.size(); i++) {
            Value3 value3 = value3s.get(i);
            XSSFRow row = sheet.createRow(i);
            row.createCell(0).setCellValue(value3.value1);
            row.createCell(1).setCellValue(value3.value2);
            row.createCell(2).setCellValue(value3.value3);
        }

        try {
            FileOutputStream fos = new FileOutputStream(name);
            workbook.write(fos);
            fos.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value3s.size();
    }

    private int saveReUsPatent(String patentNum, List<Object> trs) {
        new File("被引用美国专利").mkdir();
        String name = "被引用美国专利/" + patentNum + ".xlsx";

        List<Value3> value3s = new ArrayList<>();
        for (Object tr : trs) {
            JXDocument jxDocument = JXDocument.create("<table>" + tr.toString() + "</table>");
            Object o1 = jxDocument.selOne("//tr/td[1]/a/text()");
            Object o2 = jxDocument.selOne("//tr/td[2]/text()");
            Object o3 = jxDocument.selOne("//tr/td[3]/text()");
            if (o1 == null && o2 == null && o3 == null) {
                continue;
            }

            String value1 = "";
            String value2 = "";
            String value3 = "";
            if (o1 != null) {
                value1 = o1.toString().trim();
            }
            if (o2 != null) {
                value2 = o2.toString().trim();
            }
            if (o3 != null) {
                value3 = o3.toString().trim();
            }

            if (value1.equals("") && value2.equals("") && value3.equals("")) {
                continue;
            }

            value3s.add(new Value3(value1, value2, value3));
        }

        if (value3s.size() == 0) {
            return 0;
        }

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();

        for (int i = 0; i < value3s.size(); i++) {
            Value3 value3 = value3s.get(i);
            XSSFRow row = sheet.createRow(i);
            row.createCell(0).setCellValue(value3.value1);
            row.createCell(1).setCellValue(value3.value2);
            row.createCell(2).setCellValue(value3.value3);
        }

        try {
            FileOutputStream fos = new FileOutputStream(name);
            workbook.write(fos);
            fos.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value3s.size();
    }

    public Connection createConnection() {
        Connection conn = null;
        try {
            Class.forName("com.hxtt.sql.access.AccessDriver");
            conn = DriverManager.getConnection("jdbc:Access:///" + dataUrl);
        } catch (Exception e) {
            e.printStackTrace();
        }

        Assert.assertTrue(conn != null);
        return conn;
    }

    public void closeConn(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void test6() {
        Html html = new Html("<a>你好</a>");
        String s = html.xpath("//a").regex(".*你好.*").get();
        System.out.println(s);
    }

    public void test7() {
        JXDocument jxDocument = JXDocument.create("<div><p>p1</p><a>你好</a><p>p2</p><p>p3</p></div>");
        Object o = jxDocument.selOne("//a[text()='你好']");
        o = jxDocument.sel("//a[contains(text(), '你好')]/following-sibling::p");
        System.out.println(o);
    }

    class Value3 {
        public String value1 = "";
        public String value2 = "";
        public String value3 = "";

        public Value3(String value1, String value2, String value3) {
            this.value1 = value1;
            this.value2 = value2;
            this.value3 = value3;
        }
    }
}
